<?php
// 数据库相关基础函数

/*查询数据库符合条件的记录条数*/
function sqlNum($table, $where): int|string
{
	$con = $GLOBALS['con'];
	if (empty($where)) {
		$where = "";
	} elseif (strtolower(substr(trim($where), 0, 3)) == "and") {
		$where = " where 1=1 " . $where;
	} else {
		$where = " where " . $where;
	}
	$sql = "select * from " . $table . $where;
	if ($query = mysqli_query($con, $sql)) {
		$num = mysqli_num_rows($query);
		if ($num > 0) {
		} else {
			$num = 0; //数据库查询语句有误时$num为空
		}
	} else {
		$num = "数据查询失败：" . $sql;
	}
	return $num;
}

/*查询数据库表内容
$data = array(
    "table" =>*"需要查询的数据库表名",
    "field" => "需要返回的字段，未设置时返回所有字段",
    "sum" => "给某个字段求和",
    "where" => "查询条件，未设置时返回所有记录",
    "single" => "唯一数组查询",
);
*/
function find(array $data): float|array|string
{
	$con = $GLOBALS['con'];
	if (empty($data['table'])) {
		$result = "数据库表名称为空";
	} else {
		//返回的字段
		if (empty($data['field'])) {
			if (isset($data['sum'])) {
				$data['field'] = " ifnull(sum(" . $data['sum'] . "),0) as " . $data['sum'];
			} else {
				$data['field'] = "*";
			}
		}
		//查询条件
		$where = '';
		if (isset($data['where']) && $data['where']) {
			$where = $data['where'];
		} elseif (isset($data['single'])) {
			$where = $data['single'];
		}
		
		if (!empty($where)) {
			$temp = strtolower(trim($where));
			if (str_starts_with($temp, "and") or str_starts_with($temp, "order by")) {
				$where = " 1=1 " . $where;
			}
			$where = $where ? (" where " . $where) : '';
		}
		//执行
		$result['array'] = [];
		$result['sql']   = "select " . $data['field'] . " from " . $data['table'] . $where;
		if (isset($data['debug']) && $data['debug'] === true) {
			return $result['sql'] . ';' . PHP_EOL;
		}
		$query = mysqli_query($con, $result['sql']);
		if (!$query) {
			$result = "查询失败：" . $result['sql'];
		} elseif (isset($data['sum'])) {
			$array  = mysqli_fetch_assoc($query);
			$result = floatval((empty($result)) ? 0 : $array[$data['sum']]);
		} elseif (isset($data['single'])) {
			$result = (array)mysqli_fetch_assoc($query);
			if (isset($result['price'])) {
				$result['price'] = floatval($result['price']);
			}
		} else {
			$result['num'] = mysqli_num_rows($query);
			if ($result['num'] == 0) {
				$result['warn'] = "一条记录都没有";
			} else {
				while ($array = mysqli_fetch_assoc($query)) {
					if (isset($array['price'])) {
						$array['price'] = floatval($array['price']);
					}
					$result['array'][] = $array;
				}
			}
		}
	}
	//返回
	return $result;
}

/**
 * 查询函数
 *
 * @param string $table
 * @param string $where 条件
 * @param string $field 字段
 * @return array|string
 */
function query(string $table, string $where, string $field = '*'): array|string
{
	$info = find([
		'field'  => $field,
		"table"  => $table,
		"single" => $where . " limit 1",
	]);
	return $info ?: setDefault($table);
}

/**
 * 解决返回数组不存在key的问题
 *
 * @param string $table
 * @return array
 */
function setDefault(string $table): array
{
	global $conf;
	$sql    = "SELECT COLUMN_NAME FROM information_schema.columns WHERE table_schema = '{$conf['databaseName']}' AND table_name = '$table' ";
	$result = sqlFind($sql);
	$field  = [];
	foreach ($result as $val) {
		if (isset($val['COLUMN_NAME'])) {
			$field[$val['COLUMN_NAME']] = '';
		}
	}
	return $field;
}


/**
 * 新增一条数据库记录
 *
 * @param array $data
 *   $data = array(
 *   "table" => "需要更新的数据库表名",
 *   "field" => "需要更新的字段数组，键名为字段名称，键值为字段值",
 *   );
 * @return string
 */
function insert(array $data): string
{
	if (empty($data['table'])) {
		$html = "数据库表名称为空";
	} elseif (empty($data['field'])) {
		$html = "数据库新增字段为空";
	} else {
		$dataType = dataType($data['table']);
		$field    = $text = $space = '';
		foreach ($data['field'] as $key => $value) {
			$field .= $space . "`$key`";
			$value = setNull($key, $value, $dataType);
			if (is_null($value)) {
				$text .= $space . "null";
			} else {
				$text .= $space . "'$value'";
			}
			$space = ', ';
		}
		$sql = " INSERT INTO `{$data['table']}` ($field) VALUES ($text)";
		if (isset($data['debug']) && $data['debug'] === true) {
			return $sql . ';' . PHP_EOL;
		}
		$bool = sqlExec($sql);
		$html = ($bool === true) ? '新增成功' : '新增失败：' . mysqli_error_msg($data['table']);
	}
	return $html;
}


/**
 * 更新数据库中某条记录的数据
 *
 * @param array $data
 *   $data = array(
 *   "table" => "需要更新的数据库表名",
 *   "field" => "需要更新的字段数组，键名为字段名称，键值为字段值",
 *   "where" => "需要更新的数据库表记录查询条件",
 *   );
 * @return string
 */
function update(array $data): string
{
	if (empty($data['table'])) {
		$html = "数据库表名称为空";
	} elseif (empty($data['field'])) {
		$html = "数据库更新字段为空";
	} elseif (empty($data['where'])) {
		$html = "数据库查询语句为空";
	} else {
		$field    = $link = '';
		$dataType = dataType($data['table']);
		foreach ($data['field'] as $key => $value) {
			$value = setNull($key, $value, $dataType);
			if (is_null($value)) {
				$field .= $link . "`$key`" . " = null ";
			} else {
				$field .= $link . "`$key`" . " = '" . $value . "'";
			}
			$link = ", ";
		}
		$sql = " UPDATE `{$data['table']}` SET $field WHERE {$data['where']}";
		if (isset($data['debug']) && $data['debug'] === true) {
			return $sql . ';' . PHP_EOL;
		}
		$bool = sqlExec($sql);
		$html = ($bool === true) ? '更新成功' : '更新失败：' . mysqli_error_msg($data['table']);
	}
	return $html;
}

/**
 * 翻译3种常见数据库错误提示成中文，其余原样返回
 *
 * @param string $table 数据库表名
 * @return string
 */
function mysqli_error_msg(string $table): string
{
	global $con, $conf;
	$error  = mysqli_error($con);
	$errMsg = $error;
	//Data too long for column 'name' at row 1
	//Out of range value for column 'list' at row 1
	//Incorrect datetime value: '2022-0119' for column 'updateTime' at row 1
	if (!empty($error)) {
		$columnSql = "SELECT COLUMN_COMMENT as text,CHARACTER_MAXIMUM_LENGTH as longth FROM information_schema.columns WHERE table_schema = '{$conf['databaseName']}' AND table_name = '$table' ";
		if (str_contains($error, 'too long')) {
			$columnErr = explode("'", $error)[1];
			$columnSql .= " AND COLUMN_NAME='$columnErr' ";
			$result    = sqlFind($columnSql)[0];
			$errMsg    = "【{$result['text']}】内容长度超出设定值：{$result['longth']} ";
		} elseif (str_contains($error, 'range value')) {
			$columnErr = explode("'", $error)[1];
			$columnSql .= " AND COLUMN_NAME='$columnErr' ";
			$result    = sqlFind($columnSql)[0];
			$errMsg    = "【{$result['text']}】数值超出设定范围 ";
		} elseif (str_contains($error, 'Incorrect date')) {
			$columnErr = explode("'", $error)[3];
			$columnSql .= " AND COLUMN_NAME='$columnErr' ";
			$result    = sqlFind($columnSql)[0];
			$errMsg    = "【{$result['text']}】日期格式有误 ";
		}
	}
	return formSub($errMsg);
}

/**
 * 获取表中字段的数据类型
 *
 * @param $table
 * @return array
 */
function dataType($table): array
{
	global $conf;
	$data   = [];
	$sql    = " SELECT COLUMN_NAME, DATA_TYPE  FROM information_schema.COLUMNS WHERE `table_name`='$table' AND `table_schema` = '{$conf['databaseName']}' ; ";
	$result = sqlFind($sql);
	foreach ($result as $array) {
		$data[$array['COLUMN_NAME']] = $array['DATA_TYPE'];
	}
	return $data;
}

function setNull(string $key, $value, $dataType)
{
	$key = $dataType[$key];
	return match ($key) {
		'datetime', 'date', 'json', 'text'    => $value ?: null,
		'int', 'bigint', 'tinyint', 'decimal' => $value ?: 0,
		default                               => $value ?? '',
	};
}


/**
 * 删除数据库记录
 *
 * @param array $data
 *    $data = array(
 *    "table" =>*"数据库表名",
 *    "where" =>*"删除条件",
 *    );
 * @return string
 */
function delete(array $data): string
{
	if (empty($data['table'])) {
		$html = "数据库表名称为空";
	} elseif (empty($data['where'])) {
		$html = "删除条件为空";
	} else {
		$sql = "DELETE FROM {$data['table']} WHERE {$data['where']}";
		if (isset($data['debug']) && $data['debug'] === true) {
			return $sql . ';' . PHP_EOL;
		}
		$bool = sqlExec($sql);
		$html = ($bool === true) ? '删除成功' : '删除失败';
	}
	return $html;
}

/**
 * 执行sql 语句
 *
 * @param string $sql
 * @param bool   $getRes 取结果
 * @return bool|mysqli_result
 */
function sqlExec(string $sql, bool $getRes = false): mysqli_result|bool
{
	$con = $GLOBALS['con'];
	$res = mysqli_query($con, $sql);
	if ($getRes === false) {
		return (bool)$res;
	} else {
		return $res;
	}
}

/**
 * SQL 语句查找
 *
 * @param string $sql
 * @return array
 */
function sqlFind(string $sql): array
{
	$res   = sqlExec($sql, true);
	$array = [];
	if ($res) {
		while ($data = mysqli_fetch_array($res, MYSQLI_ASSOC)) {
			$array[] = $data;
		}
		return $array;
	}
	return $array;
}

/**
 * 生成IN SQL 语句
 */
function whereIn($array): string
{
	$total = sizeof($array);
	$i     = 1;
	$str   = '';
	foreach ($array as $val) {
		$str .= " '$val' ";
		if ($i != $total) {
			$str .= ',';
		}
		$i++;
	}
	return $str;
}

/**
 * 生成 Where 支持多次调用
 * condition 附加 self::$where
 */
function generateWhere($where)
{
	$str = '';
	if (is_array($where)) {
		$total = sizeof($where);
		$i     = 1;
		foreach ($where as $key => $val) {
			if (str_contains($key, '.')) {
				$str .= "$key = '$val' ";
			} else {
				$str .= "`$key` = '$val' ";
			}
			if ($i != $total) {
				$str .= ' AND ';
			}
			$i++;
		}
	} else {
		$str = $where;
	}
	return $str;
}

/**
 * 按条件统计表数量
 *
 * @param array $data
 * @return int|float|string 结果
 */
function total(array $data): int|float|string
{
	$where = '';
	if (empty($data['sql'])) {
		$data['sql'] = "SELECT IFNULL(count(*), 0) AS total FROM {$data['table']} WHERE ";
	}
	// 判断是否含有where
	if (stripos($data['sql'], 'WHERE') === false) {
		$data['sql'] .= ' WHERE ';
	}
	// 判断是否含有 1=1
	if (stripos($data['sql'], '1=1') === false) {
		$temp = explode('WHERE', $data['sql']);
		// 不存在and 且 不存在 =
		if (stripos(end($temp), 'AND') === false && stripos(end($temp), '=') === false) {
			$data['sql'] .= ' 1=1 ';
		}
	}
	// 判断是否带条件
	if (!empty($data['where'])) {
		// 条件是数组
		if (is_array($data['where'])) {
			$data['where'] = generateWhere($data['where']);
		}
		// 如果条件不存在 and
		$before = substr(strtolower(trim($data['where'])), 0, 3);
		if (stripos($before, 'and') === false) {
			$endStr = substr(strtolower(trim($data['sql'])), -5, 5);
			$where  = $endStr == 'where' ? (' ' . $data['where']) : (' AND ' . $data['where']);
		} else {
			$where = $data['where'];
		}
	}
	$data['sql'] .= !empty($data['group']) ? $where . $data['group'] : $where;
	if (isset($data['debug']) && $data['debug'] === true) {
		return $data['sql'] . ';' . PHP_EOL;
	}
	$res = mysqli_fetch_assoc(sqlExec($data['sql'], true));
	return floatval($res['total']);
}
